<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="UTF-8">
	<title>kendo spreadsheet</title>
	<link rel="stylesheet" type="text/css" href="kendo.common.min.css">
	<link rel="stylesheet" type="text/css" href="kendo.silver.min.css">
	<style type="text/css" media="screen">
		*{padding: 0;margin: 0;}
	</style>
</head>
<body style="height: 100vh;">
	<div style="color: red;"> 下表导入带取数标记的表格 </div>
	<div id="spreadsheet" style="width: 100%;height:50%;"></div>
	<div style="color: red;"> 下表导入带公式的表格（结果会在此表格） </div>
	<div id="spreadsheet1" style="width: 100%;height:50%;"></div>
	<button class="do-merger">注入公式</button>
	<script src="jquery.js"></script>
	<script src="kendo.custom.js"></script>
	<script src="jszip.min.js"></script>
	<script>
		// var rowCol = [[57, 47],[33, 20],[53, 10], [17, 11], [13, 10], [9, 9], [9, 6], [27, 7], [27, 11], [25, 20]];
		// var rowCol = [[41, 16],[14, 7]];
		var rowCol = [[48, 14]];
		$("#spreadsheet").kendoSpreadsheet().data("kendoSpreadsheet");
		$("#spreadsheet1").kendoSpreadsheet().data("kendoSpreadsheet");
		$('.do-merger').on('click', function(){
			// kendo取数规则快速填充方法
			var kendoDataHasRgl = $('#spreadsheet').data('kendoSpreadsheet');
			var kendoDataHasFormula = $('#spreadsheet1').data('kendoSpreadsheet');

			var kendoJosnHasRgl = kendoDataHasRgl.toJSON();
			var kendoJosnHasFormula = kendoDataHasFormula.toJSON();

			//遍历表格
			console.time('format');
			for (var k = 0; k < kendoJosnHasRgl.sheets.length; k++) {
				var sheet = kendoJosnHasRgl.sheets[k];
				 	kendoJosnHasFormula.sheets[k].tms_rowCount = rowCol[k][0];
					kendoJosnHasFormula.sheets[k].tms_columnCount = rowCol[k][1];
				//遍历表格的行
				for(var i = 0; i < sheet.rows.length; i++){
					//遍历表格的行的每个单元格
					for (var j = 0; j < sheet.rows[i].cells.length; j++) {
						var regular = sheet.rows[i].cells[j].value;
						//  /^\{\".*\"\:\s*\".*\"\}$/gi.test('{"getPreVal":"main_10-YBXMBNLJ"}')
						if (i === 44) {
							console.log('regular',regular);
						}
						regular&& typeof regular === 'string'&& regular.replace(/^\{\"(.*)\"\:\s*\"(.*)\"\}$/gi, (match, p1, p2) => {
							if (p1 !== '' && p2 !== '') {
								var tmsCellFlag = new Object();
								tmsCellFlag[p1] = p2;
								kendoJosnHasFormula.sheets[k].rows[i].cells[j].tmsCellFlag = tmsCellFlag;//植入规则对象
								// kendoDataHasFormula.sheets[k].rows[i].cells[j].value = '';//文字为空字符
								// kendoJosnHasFormula.sheets[k].rows[i].cells[j].background = '#ff0000';//背景为红色
								//kendoJosnHasFormula.sheets[k].rows[i].cells[j].format = '#,0.00';//格式化

								console.info(`替换发生在第${k}个sheet的第${i}行的第${j}个单元格。`);
								console.info(`植入的取数规则为：${regular}`);
								console.info('------------------------------------------------------');
							}
						});

						try{
							let cell = kendoJosnHasFormula.sheets[k].rows[i].cells[j],
									cellDelAttr = [];
							if (cell) {
								cell.fontFamily === '宋体'&& (delete cell.fontFamily);
								cell.fontSize === 12&& (delete cell.fontSize);
								cell.textAlign === 'left'&& (delete cell.textAlign);
								cell.value === ''&& (delete cell.value);
								// cell.borderLeft&& (delete cell.borderLeft, cellDelAttr.push(1));
								// cell.borderRight&& (delete cell.borderRight, cellDelAttr.push(2));
								// cell.borderTop&& (delete cell.borderTop, cellDelAttr.push(3));
								// cell.borderBottom&& (delete cell.borderBottom, cellDelAttr.push(4));
							}
							// cell['cellDelAttr'] = 'xxx';
						}catch(e){
							console.log('-------------')
							console.log(`${k}---${i}---${j}`);
							console.log(kendoJosnHasFormula.sheets[k].rows[i]);
						}
					}

					kendoJosnHasFormula.sheets[k].rows[i].cells.splice(rowCol[k][1], kendoJosnHasFormula.sheets[k].rows[i].cells.length - rowCol[k][1]);
				}

				kendoJosnHasFormula.sheets[k].rows.splice(rowCol[k][0], kendoJosnHasFormula.sheets[k].rows.length - rowCol[k][0]);
			}
			console.timeEnd('format');
			console.info('结果为：');
			console.info(kendoJosnHasFormula);
			kendoDataHasFormula.fromJSON(kendoJosnHasFormula);
			
			// 8-1日更新 附表二栏次8b（E17、F17、G17），增加取数规则
			// $("#spreadsheet").data("kendoSpreadsheet").sheets()[2].range('E17').tmsCellFlag({"getSimpleVal":"JY1_JYMXGQGL_FS"})
			// $("#spreadsheet").data("kendoSpreadsheet").sheets()[2].range('F17').tmsCellFlag({"getSimpleVal":"JY2_JYMXGQGL_JE"})
			// $("#spreadsheet").data("kendoSpreadsheet").sheets()[2].range('G17').tmsCellFlag({"getSimpleVal":"JY3_JYMXGQGL_SE"})
			 
			 
			 
			//这个格设置不进去，单独来一下
			// $('#spreadsheet1').data('kendoSpreadsheet').sheets()[0].range('Z45:AI45').tmsCellFlag({"savePreVal":"main_36-YBXMBNLJ"});
			// console.log(kendoDataHasFormula.toJSON(true));
			// console.log(JSON.stringify(kendoDataHasFormula.toJSON(true)));
			// var json = kendoDataHasFormula.toJSON(true);
			// kendoDataHasFormula.fromJSON(json,true);
			// $.ajax({
	  //       url: "/api/post",
	  //       type: "POST",
	  //       datatype: "json",
	  //       contentType: "application/json; charset=utf-8",
	  //       data: JSON.stringify(kendoDataHasFormula.toJSON(true)),
	  //       success: function(data, stats) {
	  //       	alert(data);
	  //       },
	  //       error: function(data) {
	  //       }
	  //   });
		});
	</script>
</body>
</html>